package com.sp2.dev.test.hoteljxl;

import com.sp2.dev.jdbc.exception.DBConnectionNoTomcat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: zhimingli
 * Date: 14-2-7
 * Time: 上午9:26
 * To change this template use File | Settings | File Templates.
 */

public class ReadCsvMain {
    private static final Logger logger = LoggerFactory.getLogger(ReadCsvMain.class);
    private static String tableName = "hotel9";
    private static DBConnectionNoTomcat dao = new DBConnectionNoTomcat();

    private String testStr = null;

    public static void main(String[] args) throws IOException, SQLException {
        File csv = new File("E:\\zhimingli\\hotel\\最后5000.csv"); // CSV文件
        BufferedReader br = null;
        List<String> list = new ArrayList<String>();
        Connection conn = null;
        Statement stmt = null;
        int i = 0;
        String sql = null;

        br = new BufferedReader(new FileReader(csv));
        // 读取直到最后一行
        String line = "";
        conn = dao.getConnection();
        conn.setAutoCommit(false);
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        Long start = System.currentTimeMillis();
        while ((line = br.readLine()) != null) {
            CsvVO vo = getVo(line);
            if (vo == null) {
                continue;
            }
            sql = insert(vo);
            if (sql == null) {
                continue;
            }
            i++;
            stmt.addBatch(sql);
            if (i % 2500 == 0) {
                stmt.executeBatch();
                conn.setAutoCommit(true);
                stmt = null;
                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                conn.setAutoCommit(false);
            }
        }
        Long end = System.currentTimeMillis();
        System.out.println((end - start));
    }

    private static String insert(CsvVO vo) {
        StringBuffer sql = new StringBuffer();

        if (vo != null) {
            sql.append("insert into " + tableName + "(");
            sql.append("CName,CardNo,Descriot,CtfTp,CtfId,Gender,Birthday,");
            sql.append("Address,Zip,Dirty,District1,District2,District3,");
            sql.append("District4,District5,District6,FirstNm,LastNm,Duty,");
            sql.append("Mobile,Tel,Fax,EMail,Nation,Taste,");
            sql.append("Education,Company,CTel,CAddress,CZip,Family,");
            sql.append("CVersion,id");
            sql.append(") values(");
            sql.append("'" + vo.getCName() + "','" + vo.getCardNo() + "','" + vo.getDescriot() + "','" + vo.getCtfTp() + "','" + vo.getCtfId() + "','" + vo.getGender() + "','" + vo.getBirthday() + "',");
            sql.append("'" + vo.getAddress() + "','" + vo.getZip() + "','" + vo.getDirty() + "','" + vo.getDistrict1() + "','" + vo.getDistrict2() + "','" + vo.getDistrict3() + "',");
            sql.append("'" + vo.getDistrict4() + "','" + vo.getDistrict5() + "','" + vo.getDistrict6() + "','" + vo.getFirstNm() + "','" + vo.getLastNm() + "','" + vo.getDuty() + "',");
            sql.append("'" + vo.getMobile() + "','" + vo.getTel() + "','" + vo.getFax() + "','" + vo.getEMail() + "','" + vo.getNation() + "','" + vo.getTaste() + "',");
            sql.append("'" + vo.getEducation() + "','" + vo.getCompany() + "','" + vo.getCTel() + "','" + vo.getCAddress() + "','" + vo.getCZip() + "','" + vo.getFamily() + "',");
            sql.append("'" + vo.getCVersion() + "','" + vo.getId() + "'");
            sql.append(")");
        }
        return sql.toString();
    }

    private static CsvVO getVo(String line) throws UnsupportedEncodingException {
        String arr[] = line.split(",");
        CsvVO vo = new CsvVO();
        if (arr.length > 0) {
            vo.setCName(arr[0].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 1) {
            vo.setCardNo(arr[1].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 2) {
            vo.setDescriot(arr[2].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 3) {
            vo.setCtfTp(arr[3].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 4) {
            vo.setCtfId(arr[4].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 5) {
            vo.setGender(arr[5].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 6) {
            vo.setBirthday(arr[6].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 7) {
            vo.setAddress(arr[7].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 8) {
            vo.setZip(arr[8].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 9) {
            vo.setDirty(arr[9].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 10) {
            vo.setDistrict1(arr[10].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 11) {
            vo.setDistrict2(arr[11].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 12) {
            vo.setDistrict3(arr[12].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 13) {
            vo.setDistrict4(arr[13].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 14) {
            vo.setDistrict5(arr[14].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 15) {
            vo.setDistrict6(arr[15].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 16) {
            vo.setFirstNm(arr[16].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 17) {
            vo.setLastNm(arr[17].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 18) {
            vo.setDuty(arr[18].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 19) {
            vo.setMobile(arr[19].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 20) {
            vo.setTel(arr[20].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 21) {

            vo.setFax(arr[21].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 22) {
            vo.setEMail(arr[22].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 23) {
            vo.setNation(arr[23].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 24) {
            vo.setTaste(arr[24].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 25) {
            vo.setEducation(arr[25].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 26) {
            vo.setCompany(arr[26].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 27) {
            vo.setCTel(arr[27].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 28) {
            vo.setCAddress(arr[28].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 29) {
            vo.setCZip(arr[29].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 30) {
            vo.setFamily(arr[30].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 31) {
            vo.setCVersion(arr[31].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        if (arr.length > 32) {
            vo.setId(arr[32].replaceAll("'", "").replaceAll("’", "").replaceAll("\\\\",""));
        }
        return vo;
    }
}
